package com.eascs.web.o2o.web.service;

import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.eascs.web.o2o.web.dao.BiodsDao;
import com.eascs.web.o2o.web.form.O2oBPAfterLoanForm;
//import com.eascs.admin.o2o.badloan.dao.O2oBadLoanDao;
//import com.eascs.admin.o2o.badloan.entity.O2oBadLoanPageVo;
//import com.eascs.admin.o2o.badloan.entity.O2oBadRecordEntity;
//import com.eascs.admin.o2o.loan.entity.O2oEaLoanDataEnity;
//import com.eascs.admin.o2o.loan.entity.O2oEaLoanDataTdEnity;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;


/**
 * 
 * @author CXW
 *
 */
@Service
public class O2oCompensatoryService {

	@Autowired
	BiodsDao biodsDao;
	
	/**
	 * @Title: getDcHkPage
	 * @Description: 代偿回款查询
	 * @author 张庆奇
	 * @param vo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws @date
	 *             2017年4月26日 上午10:13:08
	 */
	public PageResult<Object[]> getDcHkPage(O2oBPAfterLoanForm vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getO2oIndex());
		Calendar calendar = Calendar.getInstance();
		// 获得当前时间的月份，月份从0开始所以结果要加1
		int month = calendar.get(Calendar.MONTH) + 1;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT d.bill_code_ea,l.province_name,concat_ws('-',y.ltd_code,y.ltd_name) as ltdname,");
		sql.append(" d.loaner_name,ba.loan_date,y.end_bank_date,(SELECT ppd.item_name FROM ods_o2oservice_pub_dditem ppd where ppd.dd_no='10340' and d.loan_bank=ppd.item_code) as bank_loan_name,oalfc.the_first_date,d.pay_amount,d.pay_date,");
		sql.append(" (SELECT IFNULL(sum(r.pay_amount),0)  from ods_o2oservice_badloan_record r ");
		sql.append(" where date_format(r.this_payment_date,'%Y')=date_format(date_add(now(), interval -1 year), '%Y')");
		sql.append(" and r.loan_data_id=d.id and r.flow_status='CLO' ) as tot ,");
		for (int i = month - 1; i >= 0; i--) {
			int now_month = month - i;
			sql.append(" (SELECT IFNULL(sum(r.pay_amount),0)  from ods_o2oservice_badloan_record r ");
			sql.append(" where date_format(r.this_payment_date,'%Y%m')=date_format(date_add(now(), interval -" + i + " month), '%Y%m')");
			sql.append("  and r.loan_data_id=d.id and  r.flow_status='CLO' ) as month_" + now_month + ",");
		}
		// 去除掉最后一个多余的逗号
		sql = new StringBuffer(sql.toString().substring(0, sql.toString().length() - 1));

		sql.append(" from ods_o2oservice_ea_loan_data d LEFT JOIN ods_o2oservice_platform_regional l on d.ltd=l.platform_id");
		sql.append(" LEFT JOIN ods_o2oservice_bank_loan_data ba on d.bill_code_ea=ba.bill_code");// ods_o2oservice_bank_loan_data--ba.loan_date放款日期
		sql.append(" left join ods_o2oservice_loanapply y on d.bill_code_ea=y.bill_code ");
		sql.append(" left join ods_o2oservice_after_loan_for_customer oalfc on oalfc.customer_id= y.loan_id ");
		sql.append(" where  d.pay_type='DF' ");
		
		if (StringUtil.hasText(vo.getBillCode())) {
			sql.append(" and d.bill_code_ea like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}

		if (StringUtil.hasText(vo.getLtd())) {
			sql.append(" and d.ltd = ?");
			sqlPageQuery.addParam(vo.getLtd());
		}

		if (StringUtil.hasText(vo.getLoanUserName())) {
			sql.append(" and  d.loaner_name like ?");
			sqlPageQuery.addLikeParam(vo.getLoanUserName());
		}
		if (!StringUtil.isEmpty(vo.getHostProvince())) {
			sql.append(" AND  l.province_code  = ? ");
			sqlPageQuery.addParam(vo.getHostProvince());
		}

		if (!StringUtil.isEmpty(vo.getLoanDateStart())) {
			sql.append(" AND d.loan_date >= ?");
			sqlPageQuery.addParam(vo.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(vo.getLoanDateEnd())) {
			sql.append(" AND d.loan_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getLoanDateEnd()), 1)));
		}
		if (!StringUtil.isEmpty(vo.getBankLoan())) {
			sql.append(" AND d.loan_bank = ?");
			sqlPageQuery.addParam(vo.getBankLoan());
		}

		if (!StringUtil.isEmpty(vo.getPayDateStart())) {
			sql.append(" AND d.pay_date >= ?");
			sqlPageQuery.addParam(vo.getPayDateStart());
		}
		if (!StringUtil.isEmpty(vo.getPayDateEnd())) {
			sql.append(" AND d.pay_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getPayDateEnd()), 1)));
		}

		sql.append(" order by d.pay_date desc ");
		
	    sqlPageQuery.setQuerySql(sql.toString());
		return biodsDao.findDataWithPage(sqlPageQuery);
	}

	

}
